﻿using System.Collections.Generic;
using System.Linq;

namespace XiaoSe.Data
{
    /// <summary>
    /// 生成存储过程
    /// </summary>
    public class CreateProc
    {
        /// <summary>
        /// 生成查询存储过程
        /// </summary>
        /// <returns></returns>
        public static string CreateSelect()
        {
            return "CREATE proc up_select(@tablename nvarchar(20),@where nvarchar(100)='',@orderby nvarchar(100)='')\r\nas\r\ndeclare @sql nvarchar(1000)\r\ndeclare @wheretemp nvarchar(1000)\r\ndeclare @orderbytemp nvarchar(1000)\r\nset @sql='select * from '+@tablename\r\nif @where=''\r\n  begin\r\n    set @wheretemp=' '\r\n  end\r\nelse\r\n  begin\r\n    set @wheretemp=' where '+@where\r\n  end\r\nif @orderby=''\r\n  begin\r\n    set @orderbytemp=''\r\n  end\r\nelse\r\n  begin\r\n    set @orderbytemp=@orderby\r\n  end\r\nset @sql=@sql+@wheretemp+@orderbytemp\r\nexec (@sql)\r\ngo\r\n";
        }
        /// <summary>
        /// 生成删除存储过程
        /// </summary>
        /// <returns></returns>
        public static string CreateDelete()
        {
            return "CREATE proc up_delete(@tablename nvarchar(20),@where nvarchar(100)='')\r\nas\r\ndeclare @sql nvarchar(1000)\r\nset @sql='delete from '+@tablename\r\nif @where!=''\r\n  begin\r\n    set @sql=@sql+' where '+@where\r\n  end\r\nexec (@sql)\r\ngo\r\n";
        }
        /// <summary>
        ///生成增加记录存储过程
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="fieldList">字段信息列表</param>
        /// <returns></returns>
        public static string CreateAdd(string tableName, List<FieldInfo> fieldList)
        {
            var result = "";
            result += "Create proc up_add_" + tableName + "(";
            result = fieldList.Aggregate(result, (current, fi) => current + ("@" + fi.Name + " " + fi.Type + ","));
            result = result.TrimEnd(',');
            result += ")\r\n";
            result += "as\r\n";
            result += "  insert into " + tableName + "(";
            result = fieldList.Aggregate(result, (current, fi) => current + (fi.Name + ","));
            result = result.TrimEnd(',');
            result += ")values(";
            result = fieldList.Aggregate(result, (current, fi) => current + ("@" + fi.Name + ","));
            result = result.TrimEnd(',');
            result += ")\r\ngo\r\n";
            return result;
        }
        public static string CreateUpdate(string tableName, List<FieldInfo> fieldList)
        {
            var result = "";
            result += "Create proc up_update_" + tableName + "(";
            result = fieldList.Aggregate(result, (current, fi) => current + ("@" + fi.Name + " " + fi.Type + ","));
            result = result.TrimEnd(',');
            result += ")\r\n";
            result += "as\r\n";
            result += "  update " + tableName + " set ";
            result = fieldList.Aggregate(result, (current, fi) => current + (fi.Name + "=@" + fi.Name + ","));
            result = result.TrimEnd(',');
            result += "\r\ngo\r\n";
            return result;
        }
        public static string CreateGetCount()
        {
            var result = "";
            result += "create proc GetCount(@tablename nvarchar(20))\r\n";
            result += "as\r\n";
            result += "declare @sql nvarchar(1000)\r\n";
            result += "set @sql='select count(*) from '+@tablename\r\n";
            result += "exec (@sql)\r\n";
            result += "go\r\n";
            return result;
        }
        public static string CreatePageGet()
        {
            var result = "";
            result += "alter proc PageGet(@tablename nvarchar(20),@pagesize int =10,@pageindex int =1,@where nvarchar(100)='',@orderby nvarchar(100)='',@Markkey nvarchar(20)='ID')\r\n";
            result += "as\r\n";
            result += "declare @sql nvarchar(1000)\r\n";
            result += "set @sql='select top '+convert(varchar,@pagesize)+' * from '+@tablename\r\n";
            result += "if @where!=''\r\n";
            result += "  begin\r\n";
            result += "   set @sql=@sql+' where '+@where+' and '+@Markkey+' not in ('\r\n";
            result += "  end\r\n";
            result += "else\r\n";
            result += "  begin\r\n";
            result += "   set @sql=@sql+' where '+@Markkey+' not in ('\r\n";
            result += "  end\r\n";
            result += "set @sql=@sql+'select top '+convert(varchar,(@pagesize*(@pageindex-1)))+' '+@Markkey+' from '+@tablename\r\n";
            result += "if @where!=''\r\n";
            result += "  begin\r\n";
            result += "   set @sql=@sql+' where '+@where+' '\r\n";
            result += "  end\r\n";
            result += "if @orderby!=''\r\n";
            result += "  begin\r\n";
            result += "   set @sql=@sql+@orderby\r\n";
            result += "  end\r\n";
            result += "set @sql=@sql+') '\r\n";
            result += "if @orderby!=''\r\n";
            result += "  begin\r\n";
            result += "   set @sql=@sql+@orderby\r\n";
            result += "  end\r\n";
            result += "exec (@sql)\r\ngo";
            return result;
        }
    }
    /// <summary>
    /// 字段信息，名称跟数据类型
    /// </summary>
    public class FieldInfo
    {
        public string Name { get; set; }
        public string Type { get; set; }
    }
}
